package dao;



import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import model.FornecedorMODEL;

/**
 *
 * @author WELISSON
 */
public class FornecedorDAO extends GenericDAO {
    
    private static final String INSERT = "INSERT INTO fornecedor(for_razao, for_nome, for_cnpj, for_tel, for_ramal,"
            + " for_email, log_cep, for_num) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?);";
    private static final String UPDATE = "UPDATE fornecedor SET for_razao=?, for_nome=?, for_cnpj=?, for_tel=?, "
            + "for_ramal=?, for_email=?, log_cep=?, for_num=? WHERE for_id = ?;";
    private static final String DELETE = "DELETE FROM fornecedor WHERE for_id = ?;";
    private static final String SELECT = "SELECT * FROM fornecedor ORDER BY for_razao ASC;";
    
    private static final String SELECT_BY_ID = "SELECT * FROM fornecedor WHERE for_id = ?";
    
    public int salvar(FornecedorMODEL fornecedor) throws SQLException {
        
        PreparedStatement ps = conn.prepareStatement(INSERT, PreparedStatement.RETURN_GENERATED_KEYS);
        ResultSet rs;
        int id = -1;
        
        ps.setString(1, fornecedor.getRazaoSocial());
        ps.setString(2, fornecedor.getNome());
        ps.setString(3, fornecedor.getCnpj());
        ps.setString(4, fornecedor.getTel());
        ps.setString(5, fornecedor.getRamal());
        ps.setString(6, fornecedor.getEmail());
        ps.setInt(7, fornecedor.getLogradouro());
        ps.setString(8, fornecedor.getNum());
        
        ps.executeUpdate();
        rs = ps.getGeneratedKeys();

            
        while (rs.next()) {
            id = rs.getInt(1);
        }
        rs.close();
        ps.close();
        conn.close();
        return id;
    }
    
    
    public void alterar(FornecedorMODEL fornecedor) throws SQLException  {
        
        PreparedStatement ps = conn.prepareStatement(UPDATE);
        
        ps.setString(1, fornecedor.getRazaoSocial());
        ps.setString(2, fornecedor.getNome());
        ps.setString(3, fornecedor.getCnpj());
        ps.setString(4, fornecedor.getTel());
        ps.setString(5, fornecedor.getRamal());
        ps.setString(6, fornecedor.getEmail());
        ps.setInt(7, fornecedor.getLogradouro());
        ps.setString(8, fornecedor.getNum());
        ps.setInt(9, fornecedor.getId());
        
        ps.execute();
        
        ps.close();
        conn.close();
    }
 
    public void excluir(int id) throws SQLException {
        
        PreparedStatement ps = conn.prepareStatement(DELETE);
        ps.setInt(1, id);
        ps.execute();
        
        ps.close();
        conn.close();
        
    }
 
    public List<FornecedorMODEL> findFornecedores() throws SQLException {
        List<FornecedorMODEL> fornecedores = new ArrayList<FornecedorMODEL>();
 
        String select = "SELECT * FROM fornecedor ORDER BY razao ASC";
 
        PreparedStatement stmt = getConn().prepareStatement(SELECT);
        
        ResultSet rs = stmt.executeQuery();
        
        while (rs.next()) {
            FornecedorMODEL fornecedor = new FornecedorMODEL();
            fornecedor.setId(rs.getInt("for_id"));
            fornecedor.setRazaoSocial(rs.getString("for_razao"));
            fornecedor.setNome(rs.getString("for_nome"));
            fornecedor.setCnpj(rs.getString("for_cnpj"));
            fornecedor.setTel(rs.getString("for_tel"));
            fornecedor.setRamal(rs.getString("for_ramal"));
            fornecedor.setEmail(rs.getString("for_email"));
            fornecedor.setLogradouro(rs.getInt("log_cep"));
            fornecedor.setNum(rs.getString("for_num"));
            
            fornecedores.add(fornecedor);
        }
 
        rs.close();
        stmt.close();
        conn.close();
        System.out.println(conn.isClosed());
 
        return fornecedores;
    }
 
    public FornecedorMODEL findById(int id) throws SQLException {
        
        FornecedorMODEL fornecedor = new FornecedorMODEL();
        PreparedStatement stmt = getConn().prepareStatement(SELECT_BY_ID);
             
        //stmt.setString(1, argumento);
        stmt.setInt(1, id);
        ResultSet rs = stmt.executeQuery();
 
        while (rs.next()) {
            fornecedor = new FornecedorMODEL();
            fornecedor.setId(rs.getInt("for_id"));
            fornecedor.setRazaoSocial(rs.getString("for_razao"));
            fornecedor.setNome(rs.getString("for_nome"));
            fornecedor.setCnpj(rs.getString("for_cnpj"));
            fornecedor.setTel(rs.getString("for_tel"));
            fornecedor.setRamal(rs.getString("for_ramal"));
            fornecedor.setEmail(rs.getString("for_email"));
            fornecedor.setLogradouro(rs.getInt("log_cep"));
            fornecedor.setNum(rs.getString("for_num"));
        }
 
        rs.close();
        stmt.close();
        conn.close();
        System.out.println(conn.isClosed());
        
        return fornecedor;
    }
    
}
